package com.nian.global.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;

/**
 * Created by nian on 2023/10/29.
 */
public class HSSFUtils {

    /**
     * 获取cell中的值，区分类型
     */
    public static String format(Cell cell) {

        if (cell == null) {
            return ConstantUtils.NULL;
        }

        String cellValue = ConstantUtils.NULL;

        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                //如果为时间格式的内容
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                    cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                    break;
                } else {
                    cellValue = HSSFUtils.removeEndZeros(String.valueOf(cell.getNumericCellValue()));
                }
                break;
            case HSSFCell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case HSSFCell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
        }
        return cellValue;
    }

    /**
     * 去除cell中值结尾多余的零
     */
    public static String removeEndZeros(String str) {

        String target = str.substring(str.indexOf('.') + 1);
        if (RegexUtils.isOnlyZeros(target)) {
            return str.substring(0, str.indexOf('.'));
        }
        return str;
    }

    /**
     * 根据 excel文件的后缀名生成 workbook 对象
     */
    public static Workbook createWorkbook(String filePath) {

        Workbook workbook = null;
        FileInputStream xlsStream = null;
        try {
            xlsStream = new FileInputStream(new File(filePath));
            String fileType = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length());
            switch (fileType) {
                case "xlsx":
                    workbook = new XSSFWorkbook(xlsStream);
                    break;
                case "xls":
                    workbook = new HSSFWorkbook(xlsStream);
                    break;
                default:
                    throw new Exception("读取 Excel 文件类型未包含！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }
}

